using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;
using PropertyExpression.ControllableQuery;

namespace LibraryCatalog
{
    public static class DB
    {
        public static QFragment InsertOrUpdate<T>(this T arg, string tableName)
        {
            return new {arg, tableName}.QFragment(@"
@if (arg.isNew) {
    @arg.param.Insert(tableName)
}
else {
    @arg.param.Update(tableName)
}");
        }

        private static readonly ConcurrentDictionary<string, Func<object, QWriter, TemplateBase>> insertDictionary =
            new ConcurrentDictionary<string, Func<object, QWriter, TemplateBase>>();

        public static QFragment Insert<T>(this T param, string tableName)
        {
            Func<object, QWriter, TemplateBase> value;
            if (!insertDictionary.TryGetValue(tableName, out value))
            {
                var list = GetColumns(tableName).Where(_ => !_.Item3).ToList().Select(_ => _.Item1);
                value = Util.BuildQFragment(typeof (T),
                    "INSERT INTO " + tableName + " ("
                        + string.Join(", ", list) + ") VALUES ("
                        + string.Join(", ", list.Select(_ => "@" + _)) + ")");
                insertDictionary[tableName] = value;
            }
            return writer => value(param, writer).Execute();
        }

        private static readonly ConcurrentDictionary<string, Func<object, QWriter, TemplateBase>> updateDictionary =
            new ConcurrentDictionary<string, Func<object, QWriter, TemplateBase>>();

        public static QFragment Update<T>(this T param, string tableName)
        {
            Func<object, QWriter, TemplateBase> value;
            if (!updateDictionary.TryGetValue(tableName, out value))
            {
                var list = GetColumns(tableName).ToList();
                value = Util.BuildQFragment(typeof (T),
                    "UPDATE " + tableName + " SET "
                        + string.Join(", ", from _ in list where !_.Item2 select _.Item1 + " = @" + _.Item1)
                        + " WHERE "
                        + string.Join(" AND ", from _ in list where _.Item2 select _.Item1 + " = @" + _.Item1));
                updateDictionary[tableName] = value;
            }
            return writer => value(param, writer).Execute();
        }

        private static readonly ConcurrentDictionary<string, Func<object, QWriter, TemplateBase>> deleteDictionary =
            new ConcurrentDictionary<string, Func<object, QWriter, TemplateBase>>();

        public static QFragment Delete<T>(this T param, string tableName)
        {
            Func<object, QWriter, TemplateBase> value;
            if (!deleteDictionary.TryGetValue(tableName, out value))
            {
                value = Util.BuildQFragment(typeof (T),
                    "DELETE FROM " + tableName + " WHERE "
                        + string.Join(" AND ",
                            from _ in GetColumns(tableName) where _.Item2 select _.Item1 + " = @" + _.Item1));
                deleteDictionary[tableName] = value;
            }
            return writer => value(param, writer).Execute();
        }

        private static IEnumerable<Tuple<string, bool, bool>> GetColumns(string tableName)
        {
            using (var connection =  new SqlConnection(QExecuter.ConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM " + tableName;
                    using (var reader = command.ExecuteReader(CommandBehavior.KeyInfo))
                    {
                        var schemaTable = reader.GetSchemaTable();
                        foreach (DataRow dataRow in schemaTable.Rows)
                            yield return Tuple.Create(
                                (string) dataRow["ColumnName"],
                                true.Equals(dataRow["IsKey"]),
                                true.Equals(dataRow["IsAutoIncrement"]));
                    }
                }
            }
        }
    }
}